﻿USE [UFDATA_001_2018]
GO
/****** Object:  StoredProcedure [dbo].[sp_cust_work_calc]    Script Date: 2022-08-11 8:55:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_cust_work_calc](@year int , @month int)
as
 
	
	--declare @year int ;
	--declare @month int ;
	

--清空当月数据	
	delete custwork_work_period where iyear = @year and imonth = @month

	delete custwork_curcb_period where iyear = @year and imonth = @month

	delete custwork_balcb_period where iyear = @year and imonth = @month

	delete custwork_avg where iyear = @year and imonth = @month
 
	delete custwork_trans_work where iyear = @year and imonth = @month 

	delete custwork_detailinfo where iyear = @year and imonth = @month 
	
--项目工程量
		 insert into custwork_work_period 
           select  '' cvencode,@year, @month, c.citemcode, 
             (select sum(ws.fcreatedqty) from custwork_works ws inner join custwork_work w on w.custwork_work_PK = ws.custwork_work_PK where ws.citemcode = c.citemcode and year(w.ddate) = @year and month(w.ddate) = @month)  as fcreatedqty, 
			 (select sum(ws.fquantity) from custwork_works ws inner join custwork_work w on w.custwork_work_PK = ws.custwork_work_PK where ws.citemcode = c.citemcode and year(w.ddate) = @year and month(w.ddate) = @month ) as fquantity,
             (select sum(ws.fcreatedqty - ws.fquantity)   
             from custwork_works ws  
             inner join custwork_work e on e.custwork_work_pk=ws.custwork_work_pk 
             where ws.citemcode = c.citemcode and (year(e.ddate) < @year or ( year(e.ddate) =@year and month(e.ddate) < @month))  ) as preqty 
			  --into custwork_work_period 
             from fitemss97 c  
              

--取当月施工成本
	insert into custwork_curcb_period 
            select year(a.dbill_date) as iyear, month(a.dbill_date) as imonth, a.citem_id, b.citemname, a.ccode, c.ccode_name, sum(a.md) as cbamount 
			--into custwork_curcb_period
            from gl_accvouch a 
            inner join fitemss97 b on b.citemcode = a.citem_id 
            inner join code c on c.ccode = a.ccode and c.iyear = a.iyear
            where a.ccode like '5404%' 
            and a.iyear = @year and iperiod = @month
            group by year(a.dbill_date) , month(a.dbill_date), a.citem_id, b.citemname, a.ccode, c.ccode_name


--取上月未结转施工成本余额
	insert into custwork_balcb_period 
            select @year as iyear , @month as imonth, a.citem_id, b.citemname, a.ccode, c.ccode_name, sum(a.md-a.mc) + ISNULL((select   mb from gl_accass acc where acc.iyperiod = 202201 and acc.ccode like '50010102%' and acc.ccode <>'50010102' and acc.ccode = a.ccode and acc.citem_id = a.citem_id),0) 	+   ISNULL((select  sum(acc.mc) 
				from gl_accvouch acc 
				where acc.iyear = @year and acc.iperiod = @month
				and acc.ccode like '50010102%' and acc.ccode <>'50010102' 
				and acc.ccode = a.ccode and acc.citem_id = a.citem_id),0) as balamount 
			--into custwork_balcb_period 
            from gl_accvouch a 
            inner join fitemss97 b on b.citemcode = a.citem_id 
            inner join code c on c.ccode = a.ccode and c.iyear = a.iyear
            where a.ccode like '50010102%' and a.ccode <>'50010102'
            and a.iperiod != 0 and  (a.iyear <@year or (a.iyear = @year and iperiod <= @month))
            group by a.citem_id,  b.citemname, a.ccode, c.ccode_name

--计算单位方量成本
	insert into custwork_avg
	select a.iyear, a.imonth, a.citem_id, a.citemname, a.ccode, 
		CASE (ISNULL(b.preqty,0) + ISNULL(fcreatedqty,0)) WHEN 0 THEN 0 ELSE Round(a.balamount / (ISNULL(b.preqty,0) + ISNULL(fcreatedqty,0)),6) END avgAmount
	--into custwork_avg
	from custwork_balcb_period a 
	left join custwork_work_period b on b.citemcode = a.citem_id and b.iyear = a.iyear and b.imonth = a.imonth
	where a.iyear = @year and a.imonth = @month

--计算本月结转成本
	insert into custwork_trans_work
    select a.iyear, a.imonth, a.citemcode, b.ccode,  a.fquantity * b.avgAmount as transAmount
	--into cust_trans_work
	from custwork_work_period a 
	left join custwork_avg b on b.iyear = a.iyear and b.imonth = a.imonth and b.citem_id = a.citemcode

--本月报表
	--项目 拆装
	insert into custwork_detailinfo(iyear, imonth, citemcode, citemname, djqty, ccqty, preqty, balqty)

	select @year, @month, f.citemcode, f.citemname, ISNULL(b.fcreatedqty,0), ISNULL(b.fquantity,0), ISNULL(b.preqty,0), ISNULL(b.preqty,0) +ISNULL(b.fcreatedqty,0)- ISNULL(b.fquantity,0)
             from fitemss97 f left join  custwork_work_period  b on b.citemcode = f.citemcode and b.iyear = @year and b.imonth =@month
     --本月施工成本  
   update a set a.curcb1 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540401'
   update a set a.curcb2 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540402'
   update a set a.curcb3 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540403'
   update a set a.curcb4 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540404'
   update a set a.curcb5 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540405'
   update a set a.curcb6 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540406'
   update a set a.curcb7 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540407'
   update a set a.curcb8 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540408'
   update a set a.curcb9 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540409'
   update a set a.curcb10 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540410'
   update a set a.curcb11 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540411'
   update a set a.curcb12 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540412'
   update a set a.curcb13 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540413'
   update a set a.curcb14 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540414'
   update a set a.curcb15 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540415'
   update a set a.curcb16 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540416'
   update a set a.curcb17 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540417'
   update a set a.curcb18 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540418'
   update a set a.curcb19 = b.cbamount from custwork_detailinfo a inner join custwork_curcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '540419'
   
   update custwork_detailinfo set curcb = ISNULL(curcb1,0) +  ISNULL(curcb2,0) +ISNULL(curcb3,0) +ISNULL(curcb4,0) + ISNULL(curcb5,0) + ISNULL(curcb6,0) + ISNULL(curcb7,0) + ISNULL(curcb8,0) +ISNULL(curcb9,0) + ISNULL(curcb10,0) + ISNULL(curcb11,0) + ISNULL(curcb12,0) + ISNULL(curcb13,0) + ISNULL(curcb14,0) + ISNULL(curcb15,0) + ISNULL(curcb16,0) + ISNULL(curcb17,0) + ISNULL(curcb18,0) + ISNULL(curcb19,0)
   where iyear = @year and imonth=@month

   --上月成本
   update a set a.jzqcb1 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010201'
   update a set a.jzqcb2 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010202'
   update a set a.jzqcb3 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010203'
   update a set a.jzqcb4 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010204'
   update a set a.jzqcb5 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010205'
   update a set a.jzqcb6 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010206'
   update a set a.jzqcb7 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010207'
   update a set a.jzqcb8 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010208'
   update a set a.jzqcb9 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010209'
   update a set a.jzqcb10 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010210'
   update a set a.jzqcb11 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010211'
   update a set a.jzqcb12 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010212'
   update a set a.jzqcb13 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010213'
   update a set a.jzqcb14 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010214'
   update a set a.jzqcb15 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010215'
   update a set a.jzqcb16 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010216'
   update a set a.jzqcb17 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010217'
   update a set a.jzqcb18 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010218'
   update a set a.jzqcb19 = b.balamount from custwork_detailinfo a inner join custwork_balcb_period b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010219'
   
     update custwork_detailinfo set jzqcb = ISNULL(jzqcb1,0) +  ISNULL(jzqcb2,0) +ISNULL(jzqcb3,0) +ISNULL(jzqcb4,0) + ISNULL(jzqcb5,0) + ISNULL(jzqcb6,0) + ISNULL(jzqcb7,0) + ISNULL(jzqcb8,0) +ISNULL(jzqcb9,0) + ISNULL(jzqcb10,0) + ISNULL(jzqcb11,0) + ISNULL(jzqcb12,0) + ISNULL(jzqcb13,0) + ISNULL(jzqcb14,0) + ISNULL(jzqcb15,0) + ISNULL(jzqcb16,0) + ISNULL(jzqcb17,0) + ISNULL(jzqcb18,0) + ISNULL(jzqcb19,0)
   where iyear = @year and imonth=@month

   --当月结转成本

   update a set a.jzcb1 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010201'
   update a set a.jzcb2 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010202'
   update a set a.jzcb3 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010203'
   update a set a.jzcb4 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010204'
   update a set a.jzcb5 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010205'
   update a set a.jzcb6 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010206'
   update a set a.jzcb7 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010207'
   update a set a.jzcb8 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010208'
   update a set a.jzcb9 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010209'
   update a set a.jzcb10 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010210'
   update a set a.jzcb11 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010211'
   update a set a.jzcb12 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010212'
   update a set a.jzcb13 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010213'
   update a set a.jzcb14 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010214'
   update a set a.jzcb15 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010215'
   update a set a.jzcb16 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010216'
   update a set a.jzcb17 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010217'
   update a set a.jzcb18 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010218'
   update a set a.jzcb19 = b.transamount from custwork_detailinfo a inner join custwork_trans_work b on b.iyear = a.iyear and b.imonth=a.imonth and b.citemcode = a.citemcode and b.ccode = '5001010219'
   
     update custwork_detailinfo set jzcb = ISNULL(jzcb1,0) +  ISNULL(jzcb2,0) +ISNULL(jzcb3,0) +ISNULL(jzcb4,0) + ISNULL(jzcb5,0) + ISNULL(jzcb6,0) + ISNULL(jzcb7,0) + ISNULL(jzcb8,0) +ISNULL(jzcb9,0) + ISNULL(jzcb10,0) + ISNULL(jzcb11,0) + ISNULL(jzcb12,0) + ISNULL(jzcb13,0) + ISNULL(jzcb14,0) + ISNULL(jzcb15,0) + ISNULL(jzcb16,0) + ISNULL(jzcb17,0) + ISNULL(jzcb18,0) + ISNULL(jzcb19,0)
   where iyear = @year and imonth=@month

   --单位方量

   update a set a.avgcb1 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010201'
   update a set a.avgcb2 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010202'
   update a set a.avgcb3 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010203'
   update a set a.avgcb4 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010204'
   update a set a.avgcb5 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010205'
   update a set a.avgcb6 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010206'
   update a set a.avgcb7 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010207'
   update a set a.avgcb8 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010208'
   update a set a.avgcb9 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010209'
   update a set a.avgcb10 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010210'
   update a set a.avgcb11 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010211'
   update a set a.avgcb12 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010212'
   update a set a.avgcb13 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010213'
   update a set a.avgcb14 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010214'
   update a set a.avgcb15 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010215'
   update a set a.avgcb16 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010216'
   update a set a.avgcb17 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010217'
   update a set a.avgcb18 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010218'
   update a set a.avgcb19 = b.avgamount from custwork_detailinfo a inner join custwork_avg b on b.iyear = a.iyear and b.imonth=a.imonth and b.citem_id = a.citemcode and b.ccode = '5001010219'
   
     update custwork_detailinfo set avgcb = ISNULL(avgcb1,0) +  ISNULL(avgcb2,0) +ISNULL(avgcb3,0) +ISNULL(avgcb4,0) + ISNULL(avgcb5,0) + ISNULL(avgcb6,0) + ISNULL(avgcb7,0) + ISNULL(avgcb8,0) +ISNULL(avgcb9,0) + ISNULL(avgcb10,0) + ISNULL(avgcb11,0) + ISNULL(avgcb12,0) + ISNULL(avgcb13,0) + ISNULL(avgcb14,0) + ISNULL(avgcb15,0) + ISNULL(avgcb16,0) + ISNULL(avgcb17,0) + ISNULL(avgcb18,0) + ISNULL(avgcb19,0)
   where iyear = @year and imonth=@month
